# Copyright (c) 2004 Nokia 
# Programming example -- see license agreement for additional rights 
# Database example application: a sports diary. 
 
import time 
 
import e32 
import e32db 
import appuifw 
 
class SportsDiary: 
    def __init__(self, db_name): 
        try: 
            self.native_db = e32db.Dbms() 
            self.native_db.open(db_name) 
        except: 
            self.native_db.create(db_name) 
            self.native_db.open(db_name) 
            self.native_db.execute(SportsDiaryEntry.sql_create) 
     
    def get_all_entries(self): 
        dbv = e32db.Db_view() 
        dbv.prepare(self.native_db, 
                    u"SELECT * from fueling ORDER BY date DESC") 
        dbv.first_line() 
        results = [] 
        for i in range(dbv.count_line()): 
            dbv.get_line() 
            e = SportsDiaryEntry(dbv) 
            results.append(e) 
            dbv.next_line() 
        return results 
     
    def add(self, e): 
        self.native_db.execute(e.sql_add()) 
 
    def delete(self, e): 
        self.native_db.execute(e.sql_delete()) 
 
    def close(self): 
        self.native_db.close() 

class Settings:
    sql_create = u"CREATE TABLE settings (id INTEGER, currency VARCHAR, units_vol INTEGER, units_dist INTEGER, units_consump INTEGER, dateformat INTEGER)"
    sql_insert_defaults = u"INSERT INTO settings (id, currency, units_vol, units_dist, units_consump, dateformat) VALUES (1, '$', 0, 0, 0, 0)"
    dateformats                = ["dd/mm/yy", "mm/dd/yy", "dd.mm.yy", "yyyy-mm-dd"]
    strftimes_from_dateformats = ["%d/%m/%y", "%m/%d/%y", "%d.%m.%y", "%Y-%m-%d"]
    volume_units               = ["litres", "gallons (US)", "gallons (UK)"]
    distance_units             = ["miles", "km"]
    consumption_units          = ["mpg (US)", "mpg (UK)", "l/100km"]
            
    def __init__(self, db_name):
        try: 
            self.native_db = e32db.Dbms() 
            self.native_db.open(db_name) 
        except: 
            self.native_db.create(db_name) 
            self.native_db.open(db_name) 
                    
        try:
            self.read_settings_from_db()
        except:
            # first run, we need to create the table and put the default values into it
            self.native_db.execute(Settings.sql_create)
            self.native_db.execute(Settings.sql_insert_defaults)
            self.read_settings_from_db()

        
    def read_settings_from_db(self):
        dbv = e32db.Db_view()
        dbv.prepare(self.native_db, u"SELECT * FROM settings")
        dbv.first_line()
        dbv.get_line()
        self.currency = dbv.col(2)
        self.units_vol = dbv.col(3)
        self.units_dist = dbv.col(4)
        self.units_consump = dbv.col(5)
        self.dateformat = dbv.col(6)
        
    def get_currency(self):
        return unicode(self.currency)
    def get_units_vol(self):
        return unicode(Settings.volume_units[self.units_vol])
    def get_units_dist(self):
        return unicode(Settings.distance_units[self.units_dist])
    def get_units_consump(self):
        return unicode(Settings.consumption_units[self.units_consump])
    def get_dateformat_string(self):
        return Settings.strftimes_from_dateformats[self.dateformat]
    
    def set_currency(self,arg):
        self.currency = arg
    def set_units_vol(self,arg):
        self.units_vol = arg
    def set_units_dist(self,arg):
        self.units_dist = arg
    
    def commit(self):
        sql_delete_current = u"DELETE FROM settings WHERE id = 1"
        sql_insert_settings = unicode("INSERT INTO settings (id, currency, units_vol, units_dist, units_consump, dateformat) VALUES (1, '%s', %d, %d, %d, %d)" %
                                        (self.currency, self.units_vol, self.units_dist, self.units_consump, self.dateformat))
        self.native_db.execute(sql_delete_current)
        self.native_db.execute(sql_insert_settings)
        
    def get_form(self): 
        
        result = [(u"Currency", 'text', self.currency), 
                  (u"Units of volume", 'combo', ([unicode(item) for item in Settings.volume_units], self.units_vol)), 
                  (u"Units of distance", 'combo', ([unicode(item) for item in Settings.distance_units], self.units_dist)),
                  (u"Units of consumption", 'combo', ([unicode(item) for item in Settings.consumption_units], self.units_consump)),
                  (u"Date format", 'combo', ([unicode(item) for item in Settings.dateformats], self.dateformat))]
        return result 
     
    def set_from_form(self, form): 
        self.currency   = form[0][2]
        self.units_vol  = int(form[1][2][1]) 
        self.units_dist = int(form[2][2][1])
        self.units_consump = int(form[3][2][1])
        self.dateformat = int(form[4][2][1])
        
    

class SportsDiaryEntry: 
    sports = [u"Running", u"Skating", u"Biking", u"Skiing", u"Swimming"] 
    sql_create = u"CREATE TABLE fueling (date TIMESTAMP, miles UNSIGNED INTEGER, fillup FLOAT, full INTEGER, cost FLOAT, comment VARCHAR)" 
     
    # Initialize with a row from Sport_diary_db 
    def __init__(self, r=None): 
        if r:             
            self.timestamp  = r.col(1) 
            self.miles      = r.col(2) 
            self.fillup     = r.col(3) 
            self.full       = r.col(4) 
            self.cost       = r.col(5)
            self.comment    = r.col(6) 
        else: 
            self.timestamp  = time.time() 
            self.miles      = 0 
            self.fillup     = 0.0 
            self.full       = 0 
            self.cost       = 0.0
            self.comment    = u"" 
 
    def sql_add(self): 
        sql = "INSERT INTO fueling (date, miles, fillup, full, cost, comment) VALUES (#%s#,%d,%f,%d,%f,'%s')"%(e32db.format_time(self.timestamp), self.miles, self.fillup, self.full, self.cost, self.comment) 
        return unicode(sql) 
     
    def sql_delete(self): 
        sql = "DELETE FROM fueling WHERE date=#%s#" %  e32db.format_time(self.timestamp) 
        return unicode(sql) 
     
    def unixtime(self): 
        return self.timestamp 
    
    def get_miles(self):
        return self.miles
    def get_fillup(self):
        return self.fillup
    def get_cost(self):
        return self.cost
    def get_comment(self):
        return self.comment
    def get_full(self):
        return self.full
        
    def get_fillup_text(self): 
        return unicode(self.fillup) + u" litres, " + unicode(self.miles) + u" miles, £" + unicode(self.cost)
 
    def get_form(self, units_dist, units_vol, currency): 
        # Convert Unix timestamp into the form the form accepts. 
        (yr, mo, da, h, m, s, wd, jd, ds) =  time.localtime(self.timestamp) 
        m += 60*h # 60 minutes per hour 
        s += 60*m # 60 seconds per minute 
        result = [(u"Date", 'date', float(self.timestamp)), 
                  (u"Odometer reading (" + units_dist + u")", 'number', int(self.miles)), 
                  (u"Fuel amount (" + units_vol + u")", 'float', float(self.fillup)),
                  (u"Full?", 'combo', ([u"No", u"Yes"], self.full)), 
                  (u"Cost (" + currency + ")", 'float', float(self.cost))]

        result.append((u"Comment", 'text', self.comment)) 
        return result 
     
    def set_from_form(self, form): 
        (yr, mo, da, h, m, s, wd, jd, ds) =  time.localtime(self.timestamp) 
        m += 60*h # 60 minutes per hour 
        s += 60*m # 60 seconds per minute 
        self.timestamp = form[0][2] + s
        self.miles     = form[1][2] 
        self.fillup  = form[2][2] 
        self.full     = int(form[3][2][1])
        self.cost      = form[4][2]
        self.comment   = form[5][2] 
         
 

def strftime_hacked(fmt, dt):
    # this gets round the bug with pys60 that causes %y to give the wrong answer.
    return time.strftime(time.strftime('%Y', dt)[2:4].join(fmt.split('%y')), dt)


class SportsDiaryApp: 
    def __init__(self): 
        self.lock = e32.Ao_lock() 
        self.exit_flag = False 
        appuifw.app.exit_key_handler = self.abort 
        self.main_view = appuifw.Listbox([(u"Loading...", u"")],  
                                         self.handle_view_entry) 
        appuifw.app.body = self.main_view 
        self.entry_list = [] 
        
        self.submenu_lastweek  = (u"last week", lambda: self.handle_summary(time.time() - (7*86400), time.time()))

        # work out when a month ago was
        tmp = list(time.localtime())
        tmp[1] -= 1 # time.strftime works this out itself for itself if month is january
        self.submenu_lastmonth = (u"last month", lambda: self.handle_summary(int(time.mktime(tmp)), time.time()))

        # work out when a year ago was
        tmp = list(time.localtime())
        tmp[0] -= 1 # time.strftime works this out itself for itself if month is january
        self.submenu_lastyear = (u"last year", lambda: self.handle_summary(int(time.mktime(tmp)), time.time()))

        self.submenu_forever = (u"forever", lambda: self.handle_summary(0, time.time()))
        
        self.menu_add = (u"Add", self.handle_add) 
        self.menu_summary = (u"Summary", (self.submenu_lastweek, self.submenu_lastmonth, self.submenu_lastyear, self.submenu_forever)) 
        self.menu_delete = (u"Delete", self.handle_delete) 
        self.menu_settings = (u"Settings", self.handle_settings)
        appuifw.app.menu = [] 
 
    def initialize_db(self, db_name): 
        self.sports_diary = SportsDiary(db_name) 
        self.settings     = Settings(db_name)
         
    def run(self): 
        while not self.exit_flag: 
            self.show_main_view() 
            self.lock.wait() 
        self.close() 
 
    def close(self): 
        appuifw.app.menu = [] 
        appuifw.app.body = None 
        appuifw.app.exit_key_handler = None 
        self.sports_diary.close() 
 
    def abort(self): 
        self.exit_flag = True 
        self.lock.signal() 
 
    def update_entry_list(self): 
        self.entry_list = self.sports_diary.get_all_entries() 
     
    def show_main_view(self): 
        self.update_entry_list() 
        if not self.entry_list: 
            content = [(u"(Empty)", u"")] 
        else: 
            content = []
            for i in range(len(self.entry_list)):
                item = self.entry_list[i]
                try:
                    iterator = i+1
                    cumulative_fillup = 0.0
                    while not self.entry_list[iterator].get_full():
                        cumulative_fillup += self.entry_list[iterator].get_fillup()
                        iterator += 1
                    consump = self.calculate_consumption(self.entry_list[iterator].get_miles(), item.get_miles(), item.get_fillup()+cumulative_fillup)
                except: # only one entry, or no full tanks
                    consump = 0.0
                                
                datestring = strftime_hacked(self.settings.get_dateformat_string(), time.localtime(item.unixtime()))
                infostring = item.get_fillup_text() + unicode(self.settings.get_units_dist())
            
                topline    = unicode("%s, %d %s" % (datestring, item.get_miles(), self.settings.get_units_dist()))
                if item.get_full(): topline   += unicode(", %.1f %s" % (consump, self.settings.get_units_consump())) # only show mpg if it's a full tankup
                if item.get_full(): secondline = u"\u25cf  "
                else:               secondline = u"\u25cb  "
                secondline += unicode("%.2f %s @ %s%.2f" % (item.get_fillup(), self.settings.get_units_vol(), self.settings.get_currency(), item.get_cost()))

                # \u25cf = 'full
                # \u25cb = 'empty'
                content.append((topline,secondline))
 
        self.main_view.set_list(content) 
 
        if not self.entry_list: 
            appuifw.app.menu = [self.menu_add,
                                self.menu_settings] 
        else: 
            appuifw.app.menu = [self.menu_add, 
                                self.menu_delete, 
                                self.menu_summary,
                                self.menu_settings] 

    def calculate_consumption(self, odo_old, odo_new, fillup):
        delta_odo = odo_new - odo_old
        
        # convert fillup to litres
        units_vol = self.settings.get_units_vol()
        if units_vol == "litres": # litres
            litres = float(fillup)
        if units_vol == "gallons (US)": # us gal
            litres = fillup * 3.78541178
        if units_vol == "gallons (UK)": # uk gal
            litres = fillup * 4.54609188
        
        # convert odo to kilometres
        units_dist = self.settings.get_units_dist()
        if units_dist == "miles": # miles
            km = delta_odo * 1.609344
        if units_dist == "km": # km
            km = float(delta_odo)
        
        # work out what to do
        units_consump = self.settings.get_units_consump()
        if units_consump == "mpg (US)": # mpg, we should give local mpg
            return ((km/1.609344) / (litres/3.78541178))
        if units_consump == "mpg (UK)": # mpg, we should give local mpg
            return ((km/1.609344) / (litres/4.54609188))
        if units_consump == "l/100km": # l/100km
            return (100*litres / km)
        
    def handle_add(self): 
        new_entry = SportsDiaryEntry() 
        data = new_entry.get_form(self.settings.get_units_dist(), self.settings.get_units_vol(), self.settings.get_currency()) 
        flags = appuifw.FFormEditModeOnly + appuifw.FFormDoubleSpaced
        f = appuifw.Form(data, flags) 
        f.execute() 
        new_entry.set_from_form(f) 
        self.sports_diary.add(new_entry) 
        self.lock.signal() 
 
    def handle_settings(self):
        data = self.settings.get_form()
        flags = appuifw.FFormEditModeOnly + appuifw.FFormDoubleSpaced
        f = appuifw.Form(data, flags)
        f.execute()
        self.settings.set_from_form(f)
        self.settings.commit()
        self.lock.signal()
 
    def handle_delete(self): 
        if self.entry_list: 
            index = self.main_view.current() 
        if appuifw.query(u"Delete entry?", 'query'): 
            self.sports_diary.delete(self.entry_list[index]) 
        self.lock.signal() 

    def handle_summary(self, date_from, date_to): 
        print "summarizing from %d to %d" % (date_from, date_to)
        
        
             
    def handle_view_entry(self): 
        if self.entry_list: 
            index = self.main_view.current() 
            self.show_entry(self.entry_list[index]) 
        self.lock.signal() 
 
    def show_entry(self, entry): 
        data = entry.get_form(self.settings.get_units_dist(), self.settings.get_units_vol(), self.settings.get_currency()) 
        flags = appuifw.FFormViewModeOnly + appuifw.FFormDoubleSpaced
        f = appuifw.Form(data, flags) 
        f.execute() 
 
def main(): 
    app = SportsDiaryApp() 
    app.initialize_db(u"e:\\python\\SportsDiary.db") 
    app.run() 
 
if __name__ == '__main__': 
    old_title = appuifw.app.title 
    try: 
        appuifw.app.title = u"Sports diary" 
        e32.ao_yield() 
        main() 
    finally: 
        appuifw.app.title = old_title 
